package com.placement.refresh.dao;

import java.math.BigDecimal;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

import javax.sql.DataSource;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.object.BatchSqlUpdate;
import org.springframework.jdbc.support.incrementer.OracleSequenceMaxValueIncrementer;


import com.placement.refresh.model.RefreshContextBVO;
import com.placement.refresh.exception.InsufficientMetadataException;
import com.placement.refresh.exception.RefreshRuntimeException;
//import com.placement.refresh.model.ClientBVO;

import com.placement.refresh.util.Constants;
import com.placement.refresh.util.RefreshProperites;
import com.placement.refresh.util.RefreshUtil;
import com.placement.refresh.model.Job;
import com.placement.refresh.model.RefreshContextBVO;
import com.placement.refresh.model.SourceContextBVO;

import org.rssowl.core.internal.persist.News;
/**
 * @author Amit Chadha
 * 
 */
public class RefreshDAOImpl extends JdbcTemplate implements RefreshDAO {

	/* (non-Javadoc)
	 * @ 
	 */
	private int batchSize = 1000;

	public RefreshContextBVO getCurrBatchRunContext() {
		RefreshContextBVO refreshContextBVO = null;
		Date currBatchRundate = null;
		String sqlCurrBatchRun = "select context_id,batch_run_date " +
								 "from jobs.jobs_context where context_id = (select max(context_id) from jobs.jobs_context)";
		
		List list = queryForList(sqlCurrBatchRun);
		logger.debug(">> getCurrBatchRunContext>> list.size:"+ list.size());
		Iterator itr = list.iterator();
		if(itr.hasNext()){
			Map map = (Map) itr.next();
			if(map.get(CONTEXT_ID)!=null){
				refreshContextBVO = new RefreshContextBVO();
				refreshContextBVO.setContextId(new Long(((BigDecimal)map.get(CONTEXT_ID)).longValue()));
				
				if(map.get(BATCH_RUN_DATE)!=null){
					Timestamp timestamp = (Timestamp)map.get(BATCH_RUN_DATE);
					refreshContextBVO.setBatchRunDate(new Date(timestamp.getTime()));
				}
				
			}
		}
		
		return refreshContextBVO;
	}

	
	public SourceContextBVO getRefreshContext() {
		// TODO Auto-generated method stub
		return null;
	}

	public Date getSystemDate() {
		// TODO Auto-generated method stub
		return null;
	}

	public Long saveContext(RefreshContextBVO currContextBVO) {
		// TODO Auto-generated method stub
		return null;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see com.jpmc.refresh.dao.RefreshDAO#isProcessCompleted(int)
	 */
	public boolean isProcessCompleted(int processId) {
		boolean isCompleted = false;
		String sqlStepStatus = "select completed_fl from "
				+ "jobs.job_batch_control where batch_control_step_id=:BATCH_STEP_ID";
		Object[] params = { new Long(processId) };
		String strCompleted = (String) queryForObject(sqlStepStatus, params,String.class);
		logger.debug(">> isProcessCompleted>> >>processId:"+ processId+"  strCompleted:" + strCompleted);
		if (strCompleted != null && strCompleted.trim().equals(Constants.TRUE_FL)) {
			isCompleted = true;
		}
		return isCompleted;
	}
	
	public void saveJobsData(ArrayList<News> NewsBVOList, Long jobContextId) {
		
		logger.debug("Size fo the Def Reason BVO List :"+NewsBVOList.size());
		String sqlInsertReason = "insert into jobs.job_listing"
								+ "(context_Id, fTitle , fLinkText, fBaseUri, fReceiveDate, fPublishDate, " 
								+ "fModifiedDate,fComments,	fInReplyTo, fIsFlagged, fRating, fStateOrdinal," 
								+ "fGuidValue, fGuidIsPermaLink, fSource, " 
								+ "fFeedLink, fAuthor, fAttachments, fCategories, fLabels, fParentId, fDescription,fId,fProperties) "
 							    + "values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		BatchSqlUpdate batchUpdater = new BatchSqlUpdate(this.getDataSource(),
				sqlInsertReason);
		//batchUpdater.declareParameter(new SqlParameter(Types.BIGINT));
		batchUpdater.declareParameter(new SqlParameter(Types.BIGINT));
		batchUpdater.declareParameter(new SqlParameter(Types.VARCHAR));
		batchUpdater.declareParameter(new SqlParameter(Types.VARCHAR));
		batchUpdater.declareParameter(new SqlParameter(Types.VARCHAR));
		batchUpdater.declareParameter(new SqlParameter(Types.TIMESTAMP));
		batchUpdater.declareParameter(new SqlParameter(Types.TIMESTAMP));
		batchUpdater.declareParameter(new SqlParameter(Types.TIMESTAMP));
		batchUpdater.declareParameter(new SqlParameter(Types.VARCHAR));
		batchUpdater.declareParameter(new SqlParameter(Types.VARCHAR));
		batchUpdater.declareParameter(new SqlParameter(Types.VARCHAR));// Check
		batchUpdater.declareParameter(new SqlParameter(Types.BIGINT));
		batchUpdater.declareParameter(new SqlParameter(Types.BIGINT));
		batchUpdater.declareParameter(new SqlParameter(Types.VARCHAR));
		batchUpdater.declareParameter(new SqlParameter(Types.VARCHAR));		
		batchUpdater.declareParameter(new SqlParameter(Types.VARCHAR));
		batchUpdater.declareParameter(new SqlParameter(Types.VARCHAR));
		batchUpdater.declareParameter(new SqlParameter(Types.INTEGER));
		batchUpdater.declareParameter(new SqlParameter(Types.INTEGER));
		batchUpdater.declareParameter(new SqlParameter(Types.INTEGER));
		batchUpdater.declareParameter(new SqlParameter(Types.INTEGER));
		batchUpdater.declareParameter(new SqlParameter(Types.INTEGER));
		batchUpdater.declareParameter(new SqlParameter(Types.VARCHAR));
		batchUpdater.declareParameter(new SqlParameter(Types.INTEGER));
		batchUpdater.declareParameter(new SqlParameter(Types.VARCHAR));
		batchUpdater.compile();
		batchUpdater.setBatchSize(batchSize);
		//OracleSequenceMaxValueIncrementer oracleSequence = new OracleSequenceMaxValueIncrementer(
		//		this.getDataSource(), "jobs."+Constants.JOB_DATA_SEQ);
		logger.debug(">>savejobsData>> jobContextId:"+ jobContextId);
		for (int idx = 0; idx < NewsBVOList.size(); idx++) {
			News News = NewsBVOList.get(idx);
			

			if(News.getfGuidValue()!=null){
			//if(!checkExistingJobs(News, jobContextId)){
				logger.debug("Job Id  successful "+News.getfIsFlagged());				
			//	Long nextSequence = oracleSequence.nextLongValue();
				Object[] objParams = new Object[24];
			//	objParams[0] = nextSequence;
				objParams[0] = jobContextId;
				objParams[1] = News.getfTitle();  
				objParams[2] = News.getfLinkText(); 
				objParams[3] = News.getfBaseUri();
				objParams[4] = News.getfReceiveDate();
				objParams[5] = News.getfPublishDate();
				objParams[6] = News.getfModifiedDate();
				objParams[7] = News.getfComments();
				objParams[8] = News.getfInReplyTo();
				objParams[9] = News.getfIsFlagged() == true ? "T":"F";
				objParams[10] = News.getfRating();
				objParams[11] = News.getfStateOrdinal();
				objParams[12] = News.getfGuidValue();
				objParams[13] = News.getfGuidIsPermaLink()?"T":"F";
				objParams[14] =0;
			/*  objParams[1] = News.getfSource();*/
				objParams[15] = News.getfFeedLink();
				objParams[16] = 0;
				objParams[17] = 0;
				objParams[18] = 0;
				objParams[19] = 0;
			/*  objParams[17] = News.getfAuthor();
				objParams[18] = News.getfAttachments();
				objParams[19] = News.getfCategories();
				objParams[20] = News.getfLabels();*/
				objParams[20] = News.getfParentId();
				objParams[21] = News.getfDescription();
				objParams[22] = News.getfId();
				objParams[23] = News.getfProperties();
				batchUpdater.update(objParams);
				}
			
			}
//		}
		batchUpdater.flush();
	}
	
	/* (non-Javadoc)
	 * 
	 */
	public boolean checkExistingJobs(News NewsList , Long jobContextId) {

		boolean dupJob=false;
		String sqlCheckDupReason="select count(*) from JOBS.JOB_LISTING where " +
								"fGuidValue=:fGuidValue";
		
		Object[] params= {NewsList.getfGuidValue()};	
		Long jobCount = queryForLong(sqlCheckDupReason,params);
		logger.debug(">>>>checkExistingJobs "+NewsList.getfGuidValue()+":"+NewsList.getfTitle()+ " reason_count: "+jobCount);
		
			
		if(jobCount.longValue()>0){
			dupJob = true;
		}
		return dupJob;
	}


	public void saveJobListing(ArrayList<Job> jobList) {
		logger.debug("Size for the Job Data to Insert :"+jobList.size());
		String sqlInsertReason = "INSERT INTO jobs (source_id, description, company, country, state, city, zip_code, url, title, job_ref_id) " +
								"VALUES (?,?,?,?,?, ?,?,?,?,?)";
		BatchSqlUpdate batchUpdater = new BatchSqlUpdate(this.getDataSource(),
				sqlInsertReason);
		//batchUpdater.declareParameter(new SqlParameter(Types.BIGINT));
		batchUpdater.declareParameter(new SqlParameter(Types.BIGINT));
		batchUpdater.declareParameter(new SqlParameter(Types.VARCHAR));
		batchUpdater.declareParameter(new SqlParameter(Types.BIGINT));
		batchUpdater.declareParameter(new SqlParameter(Types.VARCHAR));
		batchUpdater.declareParameter(new SqlParameter(Types.VARCHAR));
		batchUpdater.declareParameter(new SqlParameter(Types.VARCHAR));
		batchUpdater.declareParameter(new SqlParameter(Types.VARCHAR));
		batchUpdater.declareParameter(new SqlParameter(Types.VARCHAR));// Check
		batchUpdater.declareParameter(new SqlParameter(Types.VARCHAR));
		batchUpdater.declareParameter(new SqlParameter(Types.BIGINT));
		batchUpdater.compile();
		batchUpdater.setBatchSize(batchSize);
		//OracleSequenceMaxValueIncrementer oracleSequence = new OracleSequenceMaxValueIncrementer(
		//		this.getDataSource(), "jobs."+Constants.JOB_DATA_SEQ);
		for (int idx = 0; idx < jobList.size(); idx++) {
			Job job = jobList.get(idx);
			if(job.getId() != 0 && job.getZipcode()!= 0){
				logger.debug(">>savejobsData>> jobId:"+ job.getId());
				if(!checkListedJobs(job.getId())){
				Object[] objParams = new Object[10];
				objParams[0] = job.getJobSource();
				objParams[1] = job.getDescription();  
				objParams[2] = job.getCompanyId(); 
				objParams[3] = "US";
				objParams[4] = job.getState();
				objParams[5] = job.getCity();
				objParams[6] = job.getZipcode();
				objParams[7] = job.getLink();
				objParams[8] = job.getTitle();
				objParams[9] = job.getId();
				System.out.print("Saving Data for Job Id:"+ job.getId());
				batchUpdater.update(objParams);
				}
			}
		}
		batchUpdater.flush();
		
	}
	
		/* (non-Javadoc)
		 * 
		 */
		public boolean checkListedJobs(Long jobRefId) {

			boolean dupJob=false;
			String sqlCheckDupReason="select count(*) from JOBS.JOBS where " +
									"job_ref_id=" + jobRefId;
			
			//Object[] params= {jobRefId};	
			Long jobCount = queryForLong(sqlCheckDupReason);
			if(jobCount.longValue()>0){
				dupJob = true;
			}
			logger.debug(">>>>checkListedJobs: "+jobRefId+" Exists: "+dupJob);
			return dupJob;
		}


		public Long findZipcode(String state, String city) {
			if (state == null || city == null)
				return 0L;
			logger.debug("Getting zipcode for state: " + state + " and city: " + city);
			Long zipcode = 0L;
			String sqlCheckDupReason="select min(zipcode) from zipcodes " +
				" where state like  '" + state + "%'" +
				" and city like '" + city + "%'";
			
			//Object[] params= {jobRefId};	
			Object o = queryForObject(sqlCheckDupReason, Long.class);
			
			if(o != null && o instanceof Long){
				zipcode	 = (Long)o;
			}
			logger.debug(">>>>Zipcode for location: "+zipcode);
			return zipcode;
			
		}
	
	
	
	
}
